﻿using CChaunce.Dapper.Filters.Query;
using Chaunce.FrameWork.NetCore.DapperCore;
using Dapper;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Dapper.SqlMapper;

namespace Chaunce.Dapper.Repositories
{
    public class DapperQueryRepository : IDapperQueryRepository
    {
        private readonly IContext _context;
        public DapperQueryRepository(IContext context)
        {
            _context = context;
            DapperQueryFilterExecuter = Filters.Query.DapperQueryFilterExecuter.Instance;
        }

        public IDapperQueryFilterExecuter DapperQueryFilterExecuter { get; set; }

        public virtual DbConnection Connection
        {
            get { return _context.Connection as DbConnection; }
        }

        public virtual DbTransaction ActiveTransaction
        {
            get { return _context.ActiveTransaction as DbTransaction; }
        }

        #region 事务隔离级别设置
        /// <summary>
        /// 脏读
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="acquire"></param>
        /// <param name="withNoLock"></param>
        /// <returns></returns>
        protected T ReadUncommitted<T>(Func<T> acquire, bool withNoLock)
        {
            if (withNoLock)
            {
                this.Execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
            }
            try
            {
                return acquire();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (withNoLock)
                {
                    this.Execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
                }
            }
        }

        /// <summary>
        /// 异步脏读
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="acquire"></param>
        /// <param name="withNoLock"></param>
        /// <returns></returns>
        protected async Task<T> ReadUncommittedAsync<T>(Func<Task<T>> acquire, bool withNoLock)
        {
            if (withNoLock)
            {
                this.Execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
            }
            try
            {
                var result = await acquire();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (withNoLock)
                {
                    this.Execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
                }
            }
        }
        #endregion

        #region list

        public IList<TAny> GetList<TAny>(string sql, DynamicParameters parameters = null, bool withNoLock = true) where TAny : class
        {
            Func<IList<TAny>> acquire = (() =>
            {
                return Connection.Query<TAny>(sql, parameters, ActiveTransaction).ToList();
            });
            return ReadUncommitted(acquire, withNoLock);
        }

        public async Task<IList<TAny>> GetListAsync<TAny>(string sql, DynamicParameters parameters, bool withNoLock = true) where TAny : class
        {
            Func<Task<IList<TAny>>> acquire = (async () =>
            {
                var data = await Connection.QueryAsync<TAny>(sql, parameters, ActiveTransaction);
                return data.ToList();
            });
            return await ReadUncommittedAsync(acquire, withNoLock);
        }

        public IList<TAny> GetList<TAny>(string query, object parameters = null, bool withNoLock = true) where TAny : class
        {
            Func<IList<TAny>> acquire = (() =>
            {
                return Connection.Query<TAny>(query, parameters, ActiveTransaction).ToList();
            });

            return ReadUncommitted(acquire, withNoLock);
        }

        public async Task<IList<TAny>> GetListAsync<TAny>(string query, object parameters = null, bool withNoLock = true)
        {
            Func<Task<IList<TAny>>> acquire = (async () =>
            {
                var data = await Connection.QueryAsync<TAny>(query, parameters, ActiveTransaction);
                return data.ToList();
            });

            return await ReadUncommittedAsync(acquire, withNoLock);
        }

        #endregion

        #region 分页查询
        /// <summary>
        /// 分页查询数据
        /// </summary>
        /// <typeparam name="T">返回的数据类型</typeparam>
        /// <param name="connection">sql链接</param>
        /// <param name="tables">数据库表名（多表连接时为：【[uc_AdminUser] as a with(nolock) left join  Uc_AttendanceRecord as b with(nolock) on a.[AUID]=b.[AUID]】）</param>
        /// <param name="fields">要返回的数据库字段</param>
        /// <param name="pageIndex">要查询的页面数（pageIndex=0表示第一页）</param>
        /// <param name="pageSize">每页的数据大小</param>
        /// <param name="sqlWhere">sqlWhere语句，必须包含【WHERE】关键字</param>
        /// <param name="orderBy">排序语句，必须包含升序/降序关键字，不可为空，不包含【ORDER BY】关键字</param>
        /// <param name="dynamicParameters">sqlWhere语句对应的参数</param>
        /// <returns>当前分页的数据</returns>
        public IPagedList<TAny> GetPagedListQuery<TAny>(string tables, string fields, string sqlWhere, int pageIndex, int pageSize, string orderBy, DynamicParameters parameters, bool withNoLock = true) where TAny : class
        {
            int totalCount;
            Func<IPagedList<TAny>> acquire = (() =>
            {
                string sqlText = GetPagedListSqlText(tables, fields, pageIndex, pageSize, orderBy, sqlWhere, parameters);

                var list = Connection.Query<TAny>(sqlText, parameters, ActiveTransaction);
                totalCount = parameters.Get<int?>("@RowCount") ?? 0;
                return new PagedList<TAny>()
                {
                    Items = list.ToList(),
                    PageIndex = pageIndex,
                    PageSize = pageSize,
                    TotalCount = totalCount,
                    TotalPages = GetTotalPages(totalCount, pageSize)
                };
            });
            return ReadUncommitted(acquire, withNoLock);
        }

        public async Task<IPagedList<TAny>> GetPagedListQueryAsync<TAny>(string tables, string fields, string sqlWhere, int pageIndex, int pageSize, string orderBy, DynamicParameters parameters, bool withNoLock = true) where TAny : class
        {
            int totalCount;
            Func<Task<IPagedList<TAny>>> acquire = (async () =>
            {
                string sqlText = GetPagedListSqlText(tables, fields, pageIndex, pageSize, orderBy, sqlWhere, parameters);

                var list = await Connection.QueryAsync<TAny>(sqlText, parameters, ActiveTransaction);
                totalCount = parameters.Get<int?>("@RowCount") ?? 0;
                return new PagedList<TAny>()
                {
                    Items = list.ToList(),
                    PageIndex = pageIndex,
                    PageSize = pageSize,
                    TotalCount = totalCount,
                    TotalPages = GetTotalPages(totalCount, pageSize)
                };
            });
            return await ReadUncommittedAsync(acquire, withNoLock);
        }

        /// <summary>
        /// 分页查询数据
        /// </summary>
        /// <typeparam name="T">返回的数据类型</typeparam>
        /// <param name="connection">sql链接</param>
        /// <param name="tables">数据库表名（多表连接时为：【[uc_AdminUser] as a with(nolock) left join  Uc_AttendanceRecord as b with(nolock) on a.[AUID]=b.[AUID]】）</param>
        /// <param name="fields">要返回的数据库字段</param>
        /// <param name="pageIndex">要查询的页面数（pageIndex=0表示第一页）</param>
        /// <param name="pageSize">每页的数据大小</param>
        /// <param name="sqlWhere">sqlWhere语句，必须包含【WHERE】关键字</param>
        /// <param name="orderBy">排序语句，必须包含升序/降序关键字，不可为空，不包含【ORDER BY】关键字</param>
        /// <param name="dynamicParameters">sqlWhere语句对应的参数</param>
        /// <param name="withNoLock"></param>
        /// <returns>当前分页的数据</returns>
        public IPagedList<TAny> GetPagedListQuery<TAny>(string tables, string fields, int pageIndex, int pageSize, string sqlWhere, string orderBy, DynamicParameters dynamicParameters, bool withNoLock = true)
        {

            int totalCount = 0;
            Func<IPagedList<TAny>> acquire = (() =>
            {
                string sqlText = string.Empty;
                if (dynamicParameters == null)
                    dynamicParameters = new DynamicParameters();

                dynamicParameters.Add("@RowCount", totalCount, DbType.Int32, ParameterDirection.Output);
                if (pageSize < 0)
                {
                    sqlText = string.Format(" SELECT {0} FROM {1} {2} ORDER BY {3} ; SELECT @RowCount=COUNT(*) FROM {1} {2};", fields, tables, sqlWhere, orderBy);
                }
                else
                {
                    if (pageIndex <= 0)
                    {
                        sqlText = string.Format(" SELECT TOP {0} {1} FROM {2} {3} ORDER BY {4} ; SELECT @RowCount=COUNT(*) FROM {2} {3} ", pageSize, fields, tables, sqlWhere, orderBy);
                    }
                    else
                    {
                        sqlText = string.Format(" SELECT * FROM(SELECT TOP {1} ROW_NUMBER() OVER(ORDER BY {2}) RowNumberIndex,{0} FROM {3} {4}) temTab1 WHERE RowNumberIndex > {5} ORDER BY RowNumberIndex ; SELECT @RowCount=COUNT(*) FROM {3} {4}; ", fields, (pageIndex + 1) * pageSize, orderBy, tables, sqlWhere, pageIndex * pageSize);
                    }
                }
                var list = Connection.Query<TAny>(sqlText, dynamicParameters).ToList();
                totalCount = dynamicParameters.Get<int?>("@RowCount") ?? 0;
                return new PagedList<TAny>()
                {
                    Items = list.ToList(),
                    PageIndex = pageIndex,
                    PageSize = pageSize,
                    TotalCount = totalCount,
                    TotalPages = GetTotalPages(totalCount, pageSize)
                };
            });
            return ReadUncommitted(acquire, withNoLock);
        }
        #endregion

        #region sql语句查询


        public int Execute(string query, object parameters = null)
        {
            return Connection.Execute(query, parameters, ActiveTransaction);
        }

        public async Task<int> ExecuteAsync(string query, object parameters = null)
        {
            var data = await Connection.ExecuteAsync(query, parameters, ActiveTransaction);
            return data;
        }

        public T ExecuteScalar<T>(string query, object parameters = null, bool withNoLock = false)
        {
            Func<T> acquire = (() =>
            {
                return Connection.ExecuteScalar<T>(query, parameters, ActiveTransaction);
            });
            return ReadUncommitted(acquire, withNoLock);
        }

        public async Task<T> ExecuteScalarAsync<T>(string query, object parameters = null, bool withNoLock = false)
        {
            Func<Task<T>> acquire = (async () =>
            {
                var data = await Connection.ExecuteScalarAsync<T>(query, parameters, ActiveTransaction);
                return data;
            });
            return await ReadUncommittedAsync(acquire, withNoLock);
        }
        /// <summary>
        /// 根据主键 ，查询一条记录
        /// </summary>
        /// <typeparam name="T">数据库表对应的实体类型</typeparam>
        /// <param name="connection">sql连接</param>
        /// <param name="primaryKeyName">主键字段名</param>
        /// <param name="primaryKeyValue">主键值</param>
        /// <param name="tableName">数据库表名</param>
        /// <returns>数据库表对应的实体</returns>
        public async Task<T> QueryFirstOrDefaultAsync<T>(string primaryKeyName, object primaryKeyValue, string tableName, bool withNoLock = true)
        {
            Func<Task<T>> acquire = (async () =>
            {
                string sqlText = string.Format(" SELECT TOP 1 * FROM {0} WHERE {1}=@p_1 ", tableName, primaryKeyName);
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@p_1", primaryKeyValue);

                return await Connection.QueryFirstOrDefaultAsync<T>(sqlText, parameters);
            });
            return await ReadUncommittedAsync(acquire, withNoLock);
        }

        /// <summary>
        /// sql查询返回第一条实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryKeyName"></param>
        /// <param name="primaryKeyValue"></param>
        /// <param name="tableName">参数</param>
        /// <returns></returns>
        public T QueryFirstOrDefault<T>(string sqlText, DynamicParameters parameters)
        {
            return Connection.QueryFirstOrDefault<T>(sqlText, parameters);
        }
        
        /// <summary>
        /// 执行参数化SQL并返回IDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Connection.ExecuteReader(sql, param, transaction, commandTimeout, commandType);
        }
        
        #endregion

        #region 存储过程


        public int ExecuteProc(string StoredProcedure, object parms = null)
        {
            return Connection.Execute(StoredProcedure, parms, ActiveTransaction, null, CommandType.StoredProcedure);
        }

        public async Task<int> ExecuteProcAsync(string StoredProcedure, object parms = null)
        {
            var data = await Connection.ExecuteAsync(StoredProcedure, parms, ActiveTransaction, null, CommandType.StoredProcedure);
            return data;
        }
        /// <summary>
        /// 执行存储过程，接收return值
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public int ExecuteProcWithReturn(string StoredProcedure, DynamicParameters parms = null)
        {
            if (parms == null)
            {
                parms = new DynamicParameters();
            }
            parms.Add("@return_value", 0, DbType.Int32, ParameterDirection.ReturnValue);

            Connection.Execute(StoredProcedure, parms, ActiveTransaction, null, CommandType.StoredProcedure);

            return parms.Get<int>("@return_value");
        }

        public async Task<int> ExecuteProcWithReturnAsync(string StoredProcedure, DynamicParameters parms = null)
        {
            if (parms == null)
            {
                parms = new DynamicParameters();
            }
            parms.Add("@return_value", 0, DbType.Int32, ParameterDirection.ReturnValue);

            await Connection.ExecuteAsync(StoredProcedure, parms, ActiveTransaction, null, CommandType.StoredProcedure);

            return parms.Get<int>("@return_value");
        }

        /// <summary>
        ///执行存储过程 查询单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="StoredProcedure"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public T ExecuteScalarProc<T>(string StoredProcedure, object parms = null, bool withNoLock = false)
        {
            Func<T> acquire = (() =>
            {
                var data = Connection.ExecuteScalar<T>(StoredProcedure, parms, ActiveTransaction, null, CommandType.StoredProcedure);
                return data;
            });
            return ReadUncommitted(acquire, withNoLock);
        }

        /// <summary>
        /// 异步执行存储过程 查询单个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="StoredProcedure"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public async Task<T> ExecuteScalarProcAsync<T>(string StoredProcedure, object parms = null, bool withNoLock = false)
        {
            Func<Task<T>> acquire = (async () =>
            {
                var data = await Connection.ExecuteScalarAsync<T>(StoredProcedure, parms, ActiveTransaction, null, CommandType.StoredProcedure);
                return data;
            });
            return await ReadUncommittedAsync(acquire, withNoLock);
        }
        
        /// <summary>
        /// 执行存储过程，返回list集合
        /// </summary>
        /// <typeparam name="TAny"></typeparam>
        /// <param name="StoredProcedure"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public IList<TAny> QueryProc<TAny>(string StoredProcedure, object parms = null, bool withNoLock = true) where TAny : class
        {
            Func<IList<TAny>> acquire = (() =>
            {
                return Connection.Query<TAny>(StoredProcedure, parms, ActiveTransaction, true, null, CommandType.StoredProcedure).ToList();
            });
            return ReadUncommitted(acquire, withNoLock);
        }

        public async Task<IList<TAny>> QueryProcAsync<TAny>(string StoredProcedure, object parms = null, bool withNoLock = true) where TAny : class
        {
            Func<Task<IList<TAny>>> acquire = (async () =>
            {
                var data = await Connection.QueryAsync<TAny>(StoredProcedure, parms, ActiveTransaction, null, CommandType.StoredProcedure);
                return data.ToList();
            });
            return await ReadUncommittedAsync(acquire, withNoLock);
        }

        public IPagedList<TAny> GetPagedListProc<TAny>(string StoredProcedure, string totalCountName = "CountPage", string pageIndexName = "PageIndex", string pageSizeName = "PageSize", DynamicParameters parms = null, bool withNoLock = true) where TAny : class
        {
            Func<IPagedList<TAny>> acquire = (() =>
            {
                var data = QueryProc<TAny>(StoredProcedure, parms, withNoLock);
                int totalCount = 0;
                int pageIndex = 0;
                int pageSize = 0;
                if (parms != null)
                {
                    if (!string.IsNullOrWhiteSpace(totalCountName))
                    {
                        totalCount = parms.Get<int>($"@{totalCountName}");
                    }
                    if (!string.IsNullOrWhiteSpace(pageIndexName))
                    {
                        pageIndex = parms.Get<int>($"@{pageIndexName}");
                    }
                    if (!string.IsNullOrWhiteSpace(pageSizeName))
                    {
                        pageSize = parms.Get<int>($"@{pageSizeName}");
                    }
                }
                return new PagedList<TAny>()
                {
                    Items = data.ToList(),
                    TotalCount = totalCount,
                    PageIndex = pageIndex,
                    PageSize = pageSize,
                    TotalPages = GetTotalPages(totalCount, pageSize)
                };
            });
            return ReadUncommitted(acquire, withNoLock);
        }
        
        public async Task<IPagedList<TAny>> GetPagedListProcAsync<TAny>(string StoredProcedure, string totalCountName = "CountPage", string pageIndexName = "PageIndex", string pageSizeName = "PageSize", DynamicParameters parms = null, bool withNoLock = true) where TAny : class
        {
            Func<Task<IPagedList<TAny>>> acquire = (async () =>
            {
                var data = await QueryProcAsync<TAny>(StoredProcedure, parms, withNoLock: withNoLock);
                int totalCount = 0;
                int pageIndex = 0;
                int pageSize = 0;
                if (parms != null)
                {
                    if (!string.IsNullOrWhiteSpace(totalCountName))
                    {
                        totalCount = parms.Get<int>($"@{totalCountName}");
                    }
                    if (!string.IsNullOrWhiteSpace(pageIndexName))
                    {
                        pageIndex = parms.Get<int>($"@{pageIndexName}");
                    }
                    if (!string.IsNullOrWhiteSpace(pageSizeName))
                    {
                        pageSize = parms.Get<int>($"@{pageSizeName}");
                    }
                }
                return new PagedList<TAny>()
                {
                    Items = data,
                    TotalCount = totalCount,
                    PageIndex = pageIndex,
                    PageSize = pageSize,
                    TotalPages = pageSize == 0 ? 0 : (int)Math.Ceiling(totalCount / (double)pageSize)
                };
            });
            return await ReadUncommittedAsync(acquire, withNoLock);
        }
        
        /// <summary>
        /// 执行存储过程，返回多个结果集
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public GridReader QueryProcMultiple(string StoredProcedure, object parms = null, bool withNoLock = true)
        {
            Func<GridReader> acquire = (() =>
            {
                return Connection.QueryMultiple(StoredProcedure, parms, ActiveTransaction, null, CommandType.StoredProcedure);
            });
            return ReadUncommitted(acquire, withNoLock);
        }

        /// <summary>
        /// 执行存储过程，返回多个结果集
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public async Task<GridReader> QueryProcMultipleAsync(string StoredProcedure, object parms = null, bool withNoLock = true)
        {
            Func<Task<GridReader>> acquire = (async () =>
            {
                var data = await Connection.QueryMultipleAsync(StoredProcedure, parms, ActiveTransaction, null, CommandType.StoredProcedure);
                return data;
            });
            return await ReadUncommittedAsync(acquire, withNoLock);
        }
        #endregion

        #region 辅助方法 参数组成


        private string GetPagedListSqlText(string tables, string fields, int pageIndex, int pageSize, string orderBy, string sqlWhere, DynamicParameters dp)
        {
            int totalCount = 0;

            string sqlText = string.Empty;

            if (dp == null)
                dp = new DynamicParameters();
            dp.Add("@RowCount", totalCount, DbType.Int32, ParameterDirection.Output);
            if (pageSize < 0)
            {
                sqlText = $"select {fields} from {tables} {sqlWhere} order by {orderBy};SELECT @RowCount=COUNT(*) FROM {tables} {sqlWhere}";
            }
            else
            {
                if (pageIndex <= 0)
                {
                    sqlText = string.Format(" SELECT TOP {0} {1} FROM {2} {3} ORDER BY {4} ; SELECT @RowCount=COUNT(*) FROM {2} {3} ", pageSize, fields, tables, sqlWhere, orderBy);
                }
                else
                {
                    sqlText = string.Format(" SELECT * FROM(SELECT TOP {1} ROW_NUMBER() OVER(ORDER BY {2}) RowNumberIndex,{0} FROM {3} {4}) temTab1 WHERE RowNumberIndex > {5} ORDER BY RowNumberIndex ; SELECT @RowCount=COUNT(*) FROM {3} {4}; ", fields, (pageIndex + 1) * pageSize, orderBy, tables, sqlWhere, pageIndex * pageSize);
                }
            }
            return sqlText;
        }

        /// <summary>
        /// 获取总页数
        /// </summary>
        /// <param name="totalCount">总记录数</param>
        /// <param name="pageSize">每页数量</param>
        /// <returns></returns>
        private int GetTotalPages(int totalCount, int pageSize)
        {
            return pageSize == 0 ? 0 : (int)Math.Ceiling(totalCount / (double)pageSize);
        }

        /// <summary>
        /// 根据主键 ，查询一条记录
        /// </summary>
        /// <typeparam name="T">数据库表对应的实体类型</typeparam>
        /// <param name="connection">sql连接</param>
        /// <param name="primaryKeyName">主键字段名</param>
        /// <param name="primaryKeyValue">主键值</param>
        /// <param name="tableName">数据库表名</param>
        /// <returns>数据库表对应的实体</returns>
        public T QueryFirstOrDefault<T>(string primaryKeyName, object primaryKeyValue, string tableName, bool withNoLock = true)
        {
            Func<T> acquire = (() =>
            {
                string sqlText = string.Format(" SELECT TOP 1 * FROM {0} WHERE {1}=@p_1 ", tableName, primaryKeyName);
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@p_1", primaryKeyValue);

                return Connection.QueryFirstOrDefault<T>(sqlText, parameters);
            });
            return ReadUncommitted(acquire, withNoLock);
        }

        #endregion
    }
}